﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QUAN_LY_MUON_TRA_SACH
{
    public partial class frmmuonsach : Form
    {
        public frmmuonsach()
        {
            InitializeComponent();
        }
        private void txtngsinh_TextChanged(object sender, EventArgs e)
        {

        }

        private void frmmuonsach_Load(object sender, EventArgs e)
        {
            //load du lieu len cbomathe
            DataTable bangmathe = new DataTable();
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select mathe,hten_dg from the_doc_gia",Connection.sqlConnection);
            sqlDataAdapter.Fill(bangmathe);
            cbomathe.DataSource = bangmathe;
            cbomathe.ValueMember = "mathe";
            //load du lieu len cboISBN
            DataTable bangISBN = new DataTable();
            SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter("select isbn,trangthai from dau_sach", Connection.sqlConnection);
            sqlDataAdapter1.Fill(bangISBN);
            cboISBN.DataSource = bangISBN;
            cboISBN.ValueMember = "ISBN";
            //
            txttrangthe.Enabled = false;
            txtttrangsach.Enabled = false;
            txttuasach.Enabled = false;
            txtnamxb.Enabled = false;
            txtnxb.Enabled = false;
            txtsotrang.Enabled = false;
            txtngonngu.Enabled = false;
            txtgiomuon.Text = DateTime.Now.ToLongTimeString();
            cadngaymuon.Text = DateTime.Today.ToString();
            cadngayhethan.Text = DateTime.Today.AddDays(7).ToString();
        }

        private void cbomathe_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (cbomathe.Text != "")
                {
                    SqlCommand lenh = new SqlCommand("select mathe, ttrangthe from the_doc_gia", Connection.sqlConnection);
                    SqlDataReader sqlDataReader = lenh.ExecuteReader();
                    while (sqlDataReader.Read())
                    {
                        if (cbomathe.Text == sqlDataReader.GetString(0))
                            txttrangthe.Text = sqlDataReader.GetString(1);
                    }
                    sqlDataReader.Dispose();
                }
                else
                    txttrangthe.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void cboISBN_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (cboISBN.Text != "")
                {
                    SqlCommand lenh = new SqlCommand("select ISBN,tuasach,ma_nxb,namxb,sotrang,ma_nn from dau_sach", Connection.sqlConnection);
                    SqlDataReader sqlDataReader = lenh.ExecuteReader();
                    while (sqlDataReader.Read())
                    {
                        if (cboISBN.Text == sqlDataReader.GetString(0))
                        {
                            txttuasach.Text = sqlDataReader.GetString(1);
                            txtnxb.Text = sqlDataReader.GetString(2);
                            txtnamxb.Text = sqlDataReader.GetInt32(3).ToString();
                            txtsotrang.Text = sqlDataReader.GetInt32(4).ToString();
                            txtngonngu.Text = sqlDataReader.GetString(5);
                        }
                    }
                    sqlDataReader.Dispose();
                    //load du lieu len cbosttcuon
                    DataTable bangsttcuon = new DataTable();
                    SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter("select stt_cuon from cuon_sach where isbn='" + cboISBN.Text + "'", Connection.sqlConnection);
                    sqlDataAdapter2.Fill(bangsttcuon);
                    cbosttcuon.DataSource = bangsttcuon;
                    cbosttcuon.ValueMember = "stt_cuon";
                    //load hinh len pictureBoxhinhsach
                    lenh = new SqlCommand("select path_anh from dau_sach where isbn='" + cboISBN.Text + "'", Connection.sqlConnection);
                    Image hinh = Image.FromFile(Convert.ToString(lenh.ExecuteScalar()));
                    pictureBoxhinhsach.Image = hinh;
                    cbosttcuon.Clear();
                }
                else
                {
                    txttuasach.Clear();
                    txtnxb.Clear();
                    txtnamxb.Clear();
                    txtsotrang.Clear();
                    txtngonngu.Clear();
                    Image anh = null;
                    pictureBoxhinhsach.Image = anh;
                }
            }
            catch (Exception ex)
            {
                
                MessageBox.Show("Lỗi: "+ex.Message,"Thông báo",MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }

        private void cbosttcuon_ValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (cbosttcuon.Text != "")
                {
                    SqlCommand lenh = new SqlCommand("select * from cuon_sach", Connection.sqlConnection);
                    SqlDataReader sqlDataReader = lenh.ExecuteReader();
                    while (sqlDataReader.Read())
                    {
                        if (cboISBN.Text == sqlDataReader.GetString(0) && cbosttcuon.Text == sqlDataReader.GetInt32(1).ToString())
                        {
                            txtttrangsach.Text = sqlDataReader.GetString(2);
                        }
                    }
                    sqlDataReader.Dispose();
                }
                else
                    txtttrangsach.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnthoat_Click(object sender, EventArgs e)
        {
            this.Close();
            FORM_MAIN.frmmain.btndsach_Click(sender,e);
        }

        private void btntaomoi_Click(object sender, EventArgs e)
        {
            cboISBN.Clear();
            cbosttcuon.Clear();
            txttrangthe.Clear();
            txtttrangsach.Clear();
            txttuasach.Clear();
            txtnxb.Clear();
            txtnamxb.Clear();
            txtsotrang.Clear();
            txtngonngu.Clear();
            pictureBoxhinhsach.Image = null;

        }

        private void btnchomuon_Click(object sender, EventArgs e)
        {
            //kiem tra lap phieu muon
            try
            {
                //kiem tra xem voi ngay muon va gio muon tren form muonsach cua doc gia do da co phieu muon nao chua?
                SqlCommand lenh = new SqlCommand("select stt_phieu from phieu_muon where mathe='" + cbomathe.Text + "'" + " and ngay_gio_muon='" + cadngaymuon.Text + " " + txtgiomuon.Text + "'", Connection.sqlConnection);
                object stt_phieu = lenh.ExecuteScalar();
                if (Convert.ToString(stt_phieu) == "")
                {
                    //neu chua co thi them mot phieu muon vao bang phieu muon
                    lenh = new SqlCommand("select stt_phieu from phieu_muon where ngay_gio_muon=(select max(ngay_gio_muon) from phieu_muon where mathe='" + cbomathe.Text + "')", Connection.sqlConnection);
                    int n = Convert.ToInt32(lenh.ExecuteScalar());
                    
                    lenh = new SqlCommand("select count(*) from gom_cuon where stt_phieu=" + n + " and ngay_gio_tra is null and ngay_het_han<getdate()", Connection.sqlConnection);
                    int y = Convert.ToInt32(lenh.ExecuteScalar());
                    //kiem tra doc gia co muon tre han cuon sach nao khong
                    if (y == 0)
                    {
                        lenh = new SqlCommand("insert into phieu_muon(mathe,ngay_gio_muon) values('" + cbomathe.Text + "','" + cadngaymuon.Text + " " + txtgiomuon.Text + "')", Connection.sqlConnection);
                        lenh.ExecuteNonQuery();

                        lenh = new SqlCommand("select ident_current('phieu_muon')", Connection.sqlConnection);
                        int m = Convert.ToInt32(lenh.ExecuteScalar());

                        
                        lenh = new SqlCommand("update gom_cuon set ngay_gio_tra='" + cadngaymuon.Text + " " + txtgiomuon.Text + "' where stt_phieu=" + n + " and ngay_gio_tra is null", Connection.sqlConnection);
                        lenh.ExecuteNonQuery();

                        //lenh = new SqlCommand("insert into gom_cuon select " + m + ",ISBN,stt_cuon,lan_gia_han,ngay_het_han,null,ghi_chu from gom_cuon where stt_phieu=" + n + " and ngay_gio_tra='" + cadngaymuon.Text + " " + txtgiomuon.Text+"'", Connection.sqlConnection);
                        //lenh.ExecuteNonQuery();

                        lenh = new SqlCommand("begin declare @stt_phieu int declare @ISBN char(13) declare @stt_cuon int declare @lan_gia_han int declare @ngay_het_han datetime declare @ngay_gio_tra datetime declare @ghi_chu nvarchar(30) declare contro"+m+" cursor for select " + m + ",ISBN,stt_cuon,lan_gia_han,ngay_het_han,null,ghi_chu from gom_cuon where stt_phieu=" + n + " and ngay_gio_tra='" + cadngaymuon.Text + " " + txtgiomuon.Text + "'" + " open contro"+m+" fetch next from contro"+m+" into @stt_phieu,@ISBN,@stt_cuon,@lan_gia_han,@ngay_het_han,@ngay_gio_tra,@ghi_chu while @@fetch_status=0 begin insert into gom_cuon values(@stt_phieu,@ISBN,@stt_cuon,@lan_gia_han,@ngay_het_han,@ngay_gio_tra,@ghi_chu) fetch next from contro"+m+" into @stt_phieu,@ISBN,@stt_cuon,@lan_gia_han,@ngay_het_han,@ngay_gio_tra,@ghi_chu end close contro"+m+" end", Connection.sqlConnection);
                        lenh.ExecuteNonQuery();
                        
                        lenh = new SqlCommand("insert into gom_cuon values(" + m + ",'" + cboISBN.Text + "'," + Convert.ToInt32(cbosttcuon.Text) + ",0,'" + cadngayhethan.Text + "',null,null)", Connection.sqlConnection);
                        lenh.ExecuteNonQuery();
                        MessageBox.Show("Đã ghi nhận mượn!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        btninphieu.Enabled = true;
                    }
                    else
                        MessageBox.Show("Bạn đang mượn quá hạn một số cuốn sách! \n Vì thế bạn không thể mượn thêm sách nữa!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    lenh = new SqlCommand("insert into gom_cuon values(" + Convert.ToInt32(stt_phieu) + ",'" + cboISBN.Text + "'," + Convert.ToInt32(cbosttcuon.Text) + ",0,'" + cadngayhethan.Text + "',null,null)", Connection.sqlConnection);
                    lenh.ExecuteNonQuery();
                    MessageBox.Show("Đã ghi nhận mượn!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    btninphieu.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi: "+ex.Message);
                SqlCommand lenh = new SqlCommand("select ident_current('phieu_muon')", Connection.sqlConnection);
                int m = Convert.ToInt32(lenh.ExecuteScalar());

                lenh=new SqlCommand("select stt_phieu from gom_cuon where stt_phieu="+m,Connection.sqlConnection);
                int stt_phieu=Convert.ToInt32(lenh.ExecuteScalar());

                if (stt_phieu == 0)
                {
                    lenh = new SqlCommand("delete from phieu_muon where stt_phieu=" + m, Connection.sqlConnection);
                    lenh.ExecuteNonQuery();
                }
            }
        }

        public void btninphieu_Click(object sender, EventArgs e)
        {
            mathe = cbomathe.Text;
            frmphieu_muon phieumuon = new frmphieu_muon();
            phieumuon.ShowDialog();
            btninphieu.Enabled = false;
        }
    }
}
